package com.situ.supermarket.dao;

import com.situ.supermarket.entity.Goods;
import com.situ.supermarket.util.DBUtil;
import com.situ.supermarket.util.StringUtil;
import com.situ.supermarket.vo.GoodsVO;

import java.util.List;

public class GoodsDao {


    public List<GoodsVO> select(Integer page, Integer limit, String name, String type) {
        String sql = "select goods.*,area.name 'areaName' from goods left join area on area.id=goods.area_id ";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(type)){
            sql+=" where instr(goods.name,?)>0 and instr(type,?)>0";
            sql+=" limit ?,?";
            return DBUtil.executeDQL(sql,GoodsVO.class,name,type,(page-1)*limit,limit);
        }
        if(StringUtil.isNotBlank(type)){
            sql+=" where instr(type,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,GoodsVO.class,type,(page-1)*limit,limit);
        }
        if(StringUtil.isNotBlank(name)){
            sql+=" where instr(goods.name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,GoodsVO.class,name,(page-1)*limit,limit);
        }
        sql+=" limit ?,?";
        return DBUtil.executeDQL(sql,GoodsVO.class,(page-1)*limit,limit);
    }

    public Long count(String name, String type) {
        String sql = " select count(1) from goods ";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(type)){
            sql+="  where instr(name,?)>0 and instr(type,?)>0";
            return DBUtil.executeCount(sql,name,type);
        }
        if(StringUtil.isNotBlank(type)){
            sql+="  where instr(type,?)>0";
            return DBUtil.executeCount(sql,type);
        }
        if(StringUtil.isNotBlank(name)){
            sql+="  where instr(name,?)>0";
            return DBUtil.executeCount(sql,name);
        }
        return DBUtil.executeCount(sql);
    }

    public void insert(Goods g) {
        String sql = "insert into goods(name,origin,type,cost,price,inventory,area_id) values(?,?,?,?,?,?,?)";
        DBUtil.executeDML(sql,g.getName(),g.getOrigin(),g.getType(),g.getCost(),g.getPrice(),g.getInventory(),g.getAreaId());
    }

    public void update(Goods g) {
        String sql = "update goods set name=?,origin=?,type=?,cost=?,price=?,inventory=?, area_id=? where id=?";
        DBUtil.executeDML(sql,g.getName(),g.getOrigin(),g.getType(),g.getCost(),g.getPrice(),g.getInventory(),g.getAreaId(),g.getId());
    }

    public void delete(Integer id) {
        String sql = "delete from goods where id=?";
        DBUtil.executeDML(sql,id);
    }

    public List<Goods> selectById(Integer id) {
        String sql = "select * from goods where id=?";
        return DBUtil.executeDQL(sql,Goods.class,id);
    }

    public List<Goods> selectId(Integer areaId) {
        String sql = "select * from goods where ";
        if(areaId!=null){
            sql+="area_id= "+areaId;
        }
        return DBUtil.executeDQL(sql,Goods.class);
    }
}

